package com.opal.modules.essence.service.impl;

import com.opal.domain.ReturnMessageData;
import com.opal.modules.essence.entity.StockInfo;
import com.opal.modules.essence.mapper.oracle.OracleBdMaterialMapper;
import com.opal.modules.essence.mapper.sqlServer.StockInfoMapper;
import com.opal.modules.essence.service.StockInfoService;
import com.opal.utils.CommonUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

@Service
@Transactional("sqlServerTransactionManager")
public class StockInfoServiceImpl implements StockInfoService {
    @Autowired
    private StockInfoMapper stockInfoMapper;
    @Autowired
    private OracleBdMaterialMapper oracleBdMaterialMapper;

    @Override
    public ReturnMessageData getBankListByExcel(InputStream in, String fileName) {
        //创建Excel工作薄
        try {
            Workbook work = this.getWorkbook(in, fileName);

            if (null == work) {
                return new ReturnMessageData(404,"创建Excel工作薄为空！");
            }
            Sheet sheet = work.getSheetAt(0);
            Row row = null;
            Cell cell =null;

            //获取备料日期
            row= sheet.getRow(0);
            cell=row.getCell(1);
            //判断是否数字类型：excel的日期前提也是需是数字类型
            String stockDate="";
            if(cell.getCellType()==0){
                //判断是否是日期类型
                if(DateUtil.isCellDateFormatted(cell)){
                    //转化为日期类型
                    Date d = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                    stockDate = formater.format(d);
                }
            }
            //获取组织
            //cell=row.getCell(4);
            //String pkOrg = this.isCellTypeValue(cell);
            String pkOrg ="0001O110000000001HET";
            StringBuilder importFilureName=new StringBuilder();//记录导入失败的物料编码
            int importFilureCode=0;  //查询oracle数据库物料名称为空的个数
            int importFilureEmpty=0; //物料编码为空的个数
            for(int i=2;sheet.getLastRowNum()>=i;i++) {
                row = sheet.getRow(i);
                if(row==null) {
                    continue;
                }
                StockInfo s=new StockInfo();
                s.setPkOrg(pkOrg+"");                               //组织
                s.setStockDate(stockDate);                    //备料日期
                cell = row.getCell(0);
                if (cell==null){
                    continue;
                }
                String stockCode = this.isCellTypeValue(cell);
                String materialName = oracleBdMaterialMapper.selectMaterialNameByCode(stockCode);
                if(StringUtils.isBlank(stockCode)){
                    importFilureEmpty+=1;
                    continue;
                }else if(StringUtils.isBlank(materialName)) {
                    importFilureCode+=1;
                    importFilureName.append(stockCode+",");
                    continue;
                }
                s.setStockCode(stockCode);     //物料编码
                s.setStockName(materialName);  //物料编码
                
                cell = row.getCell(1);
                s.setStockClassify(this.isCellTypeValue(cell));  //分类
                cell = row.getCell(2);
                s.setStockUnit(this.isCellTypeValue(cell));      //单位
                cell = row.getCell(3);
                s.setStockPosition(this.isCellTypeValue(cell));  //货位号
                cell = row.getCell(4);
                s.setStockBatch(this.isCellTypeValue(cell));      //批次号
                cell = row.getCell(5);
                s.setStockNum(this.isCellTypeValue(cell)); //领料数量
                cell = row.getCell(6);
                s.setStockMustNum(this.isCellTypeValue(cell)); //应备数量
                //处理id
                s.setStockId(CommonUtils.getValue());
                s.setCreateTime(CommonUtils.getCurrentDateTimeString());
                //插入数据库Stock_info
                int j = stockInfoMapper.insertSelective(s);
                if(j<0) {
                    throw new Exception("插入数据库失败！");
                }
            }
            work.close();

            String resultMessage="全部上传完成！";
            if(importFilureCode ==0 && importFilureEmpty ==0) {
                return  new ReturnMessageData(200,resultMessage);
            }else {
                resultMessage="上传完毕！上传过程中导入失败的有->";
                if(importFilureCode !=0) {
                    resultMessage+="NC系统未查询到物料名称"+importFilureCode+"个，物料编码为："+importFilureName.toString()+";";
                    if(importFilureEmpty !=0) {
                        resultMessage+="物料编码为空："+importFilureEmpty+"个";
                    }
                }else {
                    if(importFilureEmpty !=0) {
                        resultMessage+="物料编码为空："+importFilureEmpty+"个";
                    }
                }
                return new ReturnMessageData(200,resultMessage);
            }

        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            if(e.getMessage().equals("4")) {
                return new ReturnMessageData(500,"请上传excel文件！");
            }
            e.printStackTrace();
        }
        return new ReturnMessageData(500,"上传出现异常：有无更改过后缀如xls改为xlsx！");
    }

    /**
     * 判断类型，获取单元格中的值都以String返回
     * @param cell
     * @return
     */
    private String isCellTypeValue(Cell cell) {
        String value="";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                //获取小数点位置
                String val = cell.getNumericCellValue()+"";

                int index = val.indexOf(".");
                int length = val.length();
                int num=length-(index+1);
                if(num>=6) {
                    BigDecimal num3 = new BigDecimal(cell.getNumericCellValue()).setScale(6, BigDecimal.ROUND_HALF_UP);
                    value = num3+"";
                }else {
                    value=val;
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue()+"";
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            default:
                value = cell.toString();
                break;
        }
        return value;
    }


    /**
     * 判断文件格式
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("4");
        }
        return workbook;
    }

}
